SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TDDCTO_JUDI_Q01]  
/*---------------------------------------------------------*/      
/*---Empresa              : OFISIS S.A.                  --*/      
/*---Cliente              : OFISIS S.A.                  --*/      
/*---Sistema              : Tesoreria                    --*/      
/*---M¸dulo               : Empresa                      --*/      
/*---Programa             : Procedimiento para cargar    --*/      
/*---datos Descuentos Judiciales (Planilla y Liquidación)--*/            
/*---Script               : AD_TDDCTO_JUDI_Q01.sql       --*/      
/*---Nombre SP            : AD_TDDCTO_JUDI_Q01           --*/      
/*---Desarrollado por     : Luis Avila Poma              --*/      
/*---Fecha Creaci¸n       : 13/07/2009                   --*/      
/*---Base Datos           : Microsoft Sql Server         --*/      
/*---Versi¸n              : 9.0                          --*/      
/*---Invoca a SP          :                              --*/      
/*---------------------------------------------------------*/      
/*---------------------------------------------------------*/      
/*---Modificado 1 por     :         --*/      
/*---Fecha Modificacion   :         --*/  
/*---Detalle Modificacion :           --*/      
/*---------------------------------------------------------*/      
/*---------------------------------------------------------*/      
/*---Modificado 2 por     :         --*/      
/*---Fecha Modificacion   :         --*/       
/*---Detalle Modificacion :         --*/      
/*---------------------------------------------------------*/      
/*---Comentarios                                         --*/      
/*---                                                    --*/      
/*---------------------------------------------------------*/      
/*---------------------------------------------------------*/      
/*--- Drop Proc AD_TDDCTO_JUDI_Q01       --*/      
/*--- Grant Execute on AD_TDDCTO_JUDI_Q01 to public      --*/       
/*--- AD_TDDCTO_JUDI_Q01         --*/      
/*---------------------------------------------------------*/      
@ISCO_EMPR   TD_VC_002,  
@ISCO_MONE_NACI  TD_VC_003,  
@ISCO_MONE_EXTR  TD_VC_003,  
@ISTI_CAMB   TD_VC_003,  
@ISTI_ORIG   TD_VC_003,  
@ISCO_GRUP   TD_VC_008,  
@ISCO_UNID   TD_VC_003,  
@ISCO_PLAN   TD_VC_003,    
@ISCO_TRAB_INIC  TD_VC_020,      
@ISCO_TRAB_FINA  TD_VC_020,      
@INNU_ANNO   TD_IN_004_ANOS,   
@INNU_MESE   TD_IN_002_MESE,   
@ISCO_DICC_FORM  TD_VC_003,  
@ISCO_MONE_PLAN  TD_VC_003,  
@ISCO_MONE_BANC  TD_VC_003,  
@INFA_TIPO_CAMB  TD_NU_015_006,  
@IDFE_OPER   TD_DT_001  
  
As  
Begin  
  
 --Select para carga de DJ Opcion Planillas  
 Select 'S', T2.CO_TRAB, rtrim(t4.NO_APEL_PATE) +  ' '  + rtrim(t4.NO_APEL_MATE) + ' ' + rtrim(t4.NO_TRAB),  
 T1.NO_PERS_DEMA, @ISCO_MONE_PLAN,   
 IsNull(case When Isnull(t1.IM_DCTO_JUDI,0) <> 0 Then t1.IM_DCTO_JUDI      
 else ( ( T1.NU_DATO_CALC  * T1.PC_DCTO_JUDI ) / T2.PC_TOTA  ) - Isnull(T2.IM_TOTA,0)       
 end, 0) NU_DATO_CALC,   
 Round(OFITESO.dbo.FN_TCFACT_CAMB_Q02( @ISCO_MONE_PLAN,       
  @ISCO_MONE_BANC, @IDFE_OPER, IsNull(case When Isnull(t1.IM_DCTO_JUDI,0) <> 0 Then t1.IM_DCTO_JUDI      
  else ( ( T1.NU_DATO_CALC  * T1.PC_DCTO_JUDI ) / T2.PC_TOTA  ) - Isnull(T2.IM_TOTA,0)       
  end, 0), @ISTI_CAMB, @ISTI_ORIG, 'S', @ISCO_MONE_NACI, @ISCO_MONE_EXTR, @INFA_TIPO_CAMB),2)     
 From      
 ( Select  t5.CO_TRAB, t5.CO_EMPR, t5.CO_PLAN, t5.NU_ANNO, t5.NU_PERI, t5.NU_CORR_PERI,  
 t5.TI_DOCU_IDEN, t5.NU_DOCU_IDEN, t5.NO_PERS_DEMA,   
 t5.PC_DCTO_JUDI, t5.IM_DCTO_JUDI, t5.ST_RETE, t5.IM_TOPE_DEJU, t5.ST_BASE_DCTO,    
 t5.TI_AFEC, t5.ST_PAGO_RETE,    
 (Select case when Isnull(t5.IM_DCTO_JUDI,0) <> 0 then t5.IM_DCTO_JUDI      
 else ( T2.NU_DATO_CALC ) end       
 From TDTRAB_CALC T2      
 Where T2.CO_TRAB = t5.CO_TRAB    
 And T2.CO_EMPR = t5.CO_EMPR    
 And T2.CO_PLAN = t5.CO_PLAN    
 And T2.NU_ANNO = t5.NU_ANNO      
 And T2.NU_PERI = t5.NU_PERI      
 And T2.NU_CORR_PERI = t5.NU_CORR_PERI      
 And T2.CO_DICC_FORM = @ISCO_DICC_FORM  
 And T2.CO_CPTO_FORM IN ('@RETJU')) NU_DATO_CALC      
 From OFIPLAN..TDJUTR_PERI t5      
 Where t5.NU_ANNO = @INNU_ANNO  
 And t5.NU_PERI = @INNU_MESE  
 And (t5.ST_RETE = 'P' Or t5.ST_RETE = 'T')) T1,   
 ( Select  CO_TRAB, CO_EMPR, CO_PLAN, NU_ANNO, NU_PERI, NU_CORR_PERI,  
 Sum( Isnull(PC_DCTO_JUDI,0 ) ) PC_TOTA, sum( Isnull(IM_DCTO_JUDI,0) ) IM_TOTA      
 From OFIPLAN..TDJUTR_PERI t5      
 Where CO_TRAB >= @ISCO_TRAB_INIC  
 And CO_TRAB <= @ISCO_TRAB_FINA  
 And CO_EMPR = @ISCO_EMPR  
 And CO_PLAN = @ISCO_PLAN  
 And NU_ANNO  = @INNU_ANNO  
 And NU_PERI = @INNU_MESE  
 And (ST_RETE = 'P'  Or ST_RETE = 'T')      
 Group By CO_TRAB,CO_EMPR, CO_PLAN, NU_ANNO, NU_PERI, NU_CORR_PERI) T2, TMTRAB_PERI t4, TMTRAB_CALC, TRINFO_GRPL   
 Where t1.CO_TRAB >= @ISCO_TRAB_INIC  
 And t1.CO_TRAB <= @ISCO_TRAB_FINA  
 And TMTRAB_CALC.CO_EMPR  = @ISCO_EMPR  
 And TMTRAB_CALC.CO_UNID  = @ISCO_UNID  
 And TMTRAB_CALC.CO_PLAN  = @ISCO_PLAN  
 And TMTRAB_CALC.NU_ANNO  = @INNU_ANNO  
 And TMTRAB_CALC.NU_PERI  = @INNU_MESE  
 And t1.CO_TRAB = t2.CO_TRAB      
 And t1.CO_EMPR = t2.CO_EMPR    
 And t1.CO_PLAN = t2.CO_PLAN      
 And t1.NU_ANNO = t2.NU_ANNO      
 And t1.NU_PERI = t2.NU_PERI      
 And t1.NU_CORR_PERI = t2.NU_CORR_PERI      
 And t1.CO_TRAB = t4.CO_TRAB      
 And t1.NU_ANNO = t4.NU_ANNO      
 And t1.NU_PERI = t4.NU_PERI      
 And t1.NU_CORR_PERI = t4.NU_CORR_PERI         
 And t1.CO_TRAB = TMTRAB_CALC.CO_TRAB      
 And t1.CO_EMPR = TMTRAB_CALC.CO_EMPR      
 And t1.CO_PLAN = TMTRAB_CALC.CO_PLAN    
 And t1.NU_ANNO = TMTRAB_CALC.NU_ANNO      
 And t1.NU_PERI = TMTRAB_CALC.NU_PERI      
 And t1.NU_CORR_PERI = TMTRAB_CALC.NU_CORR_PERI        
 And TRINFO_GRPL.CO_EMPR = TMTRAB_CALC.CO_EMPR  
 And TRINFO_GRPL.TI_INF1 = TMTRAB_CALC.CO_UNID  
 And TRINFO_GRPL.TI_INF2 = TMTRAB_CALC.CO_PLAN  
 And TRINFO_GRPL.CO_GRUP = @ISCO_GRUP  
 And  Not Exists ( Select   T6.CO_EMPR From TDTRAB_PROC T6  
 Where  T6.CO_EMPR = TMTRAB_CALC.CO_EMPR  
 And       T6.CO_TRAB = TMTRAB_CALC.CO_TRAB  
 And       T6.CO_PLAN = TMTRAB_CALC.CO_PLAN  
 And       T6.NU_ANNO = TMTRAB_CALC.NU_ANNO  
 And       T6.NU_PERI = TMTRAB_CALC.NU_PERI  
 And       T6.ST_OPCI_TRAB = 'JUD')   
  
 UNION ALL  
  
 --Select para carga de DJ Opcion Liquidacion  
 Select 'S', T1.CO_TRAB, rtrim(T5.NO_APEL_PATE) +' '+rtrim(T5.NO_APEL_MATE)+' '+rtrim(T5.NO_TRAB) NO_TRAB,  
 T1.NO_PERS_DEMA ,T1.CO_MONE,  
 case when T1.ST_TIPO_DCJU = 'I'  
 then t1.IM_DCTO_JUDI  
 else ( (T1.NU_DATO_CALC1*T1.PC_DCTO_JUDI)/T2.PC_TOTA ) - ((Isnull(T2.IM_TOTA,0)*T1.PC_DCTO_JUDI)/T2.PC_TOTA) end NU_DATO_CALC,   
 Round(OFITESO.dbo.FN_TCFACT_CAMB_Q02( @ISCO_MONE_PLAN,  
      @ISCO_MONE_BANC, @IDFE_OPER, Isnull(case when T1.ST_TIPO_DCJU = 'I'  
      then t1.IM_DCTO_JUDI  
      else ( (T1.NU_DATO_CALC1*T1.PC_DCTO_JUDI)/T2.PC_TOTA ) - ((Isnull(T2.IM_TOTA,0)*T1.PC_DCTO_JUDI)/T2.PC_TOTA) end, 0), @ISTI_CAMB,  
      @ISTI_ORIG, 'S', @ISCO_MONE_NACI, @ISCO_MONE_EXTR, @INFA_TIPO_CAMB),2)  
 From  
 ( Select  T7.CO_TRAB, T7.CO_EMPR, T6.CO_PLAN, T7.NO_PERS_DEMA, T7.CO_MONE, T7.IM_DCTO_JUDI, T7.PC_DCTO_JUDI,  
   t7.IM_DCTO_JUDI NU_DATO_CALC1, 'I' ST_TIPO_DCJU,T6.IM_RETE_JUDI    NU_DATO_RETE, t6.CO_UNID  
 From TMLIQU_TRAB T6, TDDEJU_TRAB t7  
 where t6.CO_TRAB >= @ISCO_TRAB_INIC  
 And  t6.CO_TRAB <= @ISCO_TRAB_FINA  
 And T6.CO_EMPR = @ISCO_EMPR  
 AND T6.CO_UNID = @ISCO_UNID  
 AND T6.CO_PLAN = @ISCO_PLAN  
 AND year(t6.FE_PROC) = @INNU_ANNO  
 AND month(t6.FE_PROC) = @INNU_MESE  
 AND T6.ST_LIQU_DEFI = 'S'  
 And t7.CO_TRAB = t6.CO_TRAB  
 And t7.CO_EMPR = t6.CO_EMPR  
 And isnull( t7.IM_DCTO_JUDI, 0 ) > 0  
 And ( t7.ST_RETE = 'L' Or t7.ST_RETE = 'T' )  
 UNION ALL  
 Select  T7.CO_TRAB, T7.CO_EMPR, T6.CO_PLAN, T7.NO_PERS_DEMA, T7.CO_MONE, T7.IM_DCTO_JUDI, T7.PC_DCTO_JUDI,   
   T6.IM_RETE_JUDI  NU_DATO_CALC1, 'P' ST_TIPO_DCJU,T6.IM_RETE_JUDI      NU_DATO_RETE, t6.CO_UNID  
 From TMLIQU_TRAB T6, TDDEJU_TRAB t7  
 where t6.CO_TRAB >= @ISCO_TRAB_INIC  
 And  t6.CO_TRAB <= @ISCO_TRAB_FINA  
 And T6.CO_EMPR = @ISCO_EMPR  
 AND T6.CO_UNID = @ISCO_UNID  
 AND T6.CO_PLAN = @ISCO_PLAN  
 AND year(t6.FE_PROC) = @INNU_ANNO  
 AND month(t6.FE_PROC) = @INNU_MESE  
 AND T6.ST_LIQU_DEFI = 'S'  
 And t7.CO_TRAB = t6.CO_TRAB  
 And t7.CO_EMPR = t6.CO_EMPR  
 And isnull( t7.PC_DCTO_JUDI, 0) > 0  
 And ( t7.ST_RETE = 'L' Or t7.ST_RETE = 'T'  )  
 ) t1,  
 (  
 SELECT T5.CO_TRAB, T6.CO_PLAN, T6.CO_EMPR,  
 sum( Isnull(T5.PC_DCTO_JUDI,0 ) ) PC_TOTA, sum( Isnull(T5.IM_DCTO_JUDI,0) ) IM_TOTA  
 From TMLIQU_TRAB T6, TDDEJU_TRAB t5  
 where t6.CO_TRAB >= @ISCO_TRAB_INIC  
 And  t6.CO_TRAB <= @ISCO_TRAB_FINA  
 And T6.CO_EMPR = @ISCO_EMPR  
 AND T6.CO_UNID = @ISCO_UNID  
 AND T6.CO_PLAN = @ISCO_PLAN  
 AND year(t6.FE_PROC) = @INNU_ANNO  
 AND month(t6.FE_PROC) = @INNU_MESE  
 AND T6.ST_LIQU_DEFI = 'S'  
 And t5.CO_TRAB = t6.CO_TRAB  
 And t5.CO_EMPR = t6.CO_EMPR  
 And ( T5.ST_RETE = 'L'  Or T5.ST_RETE = 'T' )  
 group by T5.CO_TRAB, T6.CO_PLAN, T6.CO_EMPR ) t2,  
 TMLIQU_TRAB t4, TMTRAB_PERS t5, TRINFO_GRPL t3  
 where t1.CO_TRAB >= @ISCO_TRAB_INIC  
 And  t1.CO_TRAB <= @ISCO_TRAB_FINA  
 And  t1.CO_EMPR = @ISCO_EMPR  
 And  T1.CO_UNID = @ISCO_UNID  
 And  T1.CO_PLAN = @ISCO_PLAN  
 And  t1.NU_DATO_RETE > 0  
 AND  t2.CO_TRAB = t1.CO_TRAB  
 And  t2.CO_PLAN = t1.CO_PLAN  
 And  t2.CO_EMPR = t1.CO_EMPR  
 And  t4.CO_TRAB = t1.CO_TRAB  
 And  t4.CO_EMPR = t1.CO_EMPR  
 And  t4.CO_UNID = t1.CO_UNID  
 And  t4.CO_PLAN = t1.CO_PLAN  
 And  year(t4.FE_PROC) = 2009  
 And  month(t4.FE_PROC) = 6  
 And  t4.ST_LIQU_DEFI = 'S'  
 And  t5.CO_TRAB = t4.CO_TRAB  
 And  t3.CO_EMPR = t4.CO_EMPR  
 aND  t3.TI_INF1 = t4.CO_UNID  
 aND  t3.TI_INF2 = t4.CO_PLAN  
 And  T3.CO_GRUP = @ISCO_GRUP  
 And   Not Exists ( Select   T20.CO_EMPR From TDTRAB_PROC t20  
 Where  T20.CO_EMPR = T1.CO_EMPR  
 And    T20.CO_TRAB = T1.CO_TRAB  
 And    T20.CO_PLAN = T1.CO_PLAN  
 And    T20.NU_ANNO = year(t4.FE_PROC)  
 And    T20.NU_PERI = month(t4.FE_PROC)  
 And    T20.ST_OPCI_TRAB = 'JUD')  
   
END  
      
RETURN      
      
/*------------------------ Fin ------------------------------*/  
  
  
GO
